using System;
using System.Collections.Generic;
using System.Text;

using System.Data.Common;

using MakeIT.Framework.Data;
using MakeIT.Look.Imagem.Entidades;

namespace MakeIT.Look.Imagem.Negocios
{
	public partial class LookDAOT : DAOT
	{

		public BooklkifotoVO getBooklkifoto(int? idBookLkiFoto)
		{
			BooklkifotoVO booklkifotoVO = null;
			List<BooklkifotoVO> listBooklkifotoVOs = getBooklkifotos(idBookLkiFoto, null);
			if (listBooklkifotoVOs != null && listBooklkifotoVOs.Count > 0)
			{
				booklkifotoVO = listBooklkifotoVOs[0];
			}
			return booklkifotoVO;
		}

		public List<BooklkifotoVO> getBooklkifotos()
		{
			return getBooklkifotos(null, null);
		}

		public List<BooklkifotoVO> getBooklkifotos(int? idBookLkiFoto)
		{
			return getBooklkifotos(idBookLkiFoto, null);
		}

		public List<BooklkifotoVO> getBooklkifotos(int? idBookLkiFoto, int? idBookLki)
		{
			string where = "WHERE ";
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT blf.idBookLkiFoto as blf_idBookLkiFoto, blf.idBookLki as blf_idBookLki, blf.fotoMime as blf_fotoMime, blf.fotoNome as blf_fotoNome , blf.destaque as blf_destaque, blf.cadastro as blf_cadastro, blf.cadastradoPor as blf_cadastradoPor, blf.edicao as blf_edicao, blf.editadoPor as blf_editadoPor  ");
			query.AppendLine("FROM booklkifoto as blf LEFT OUTER JOIN booklki as blk on blf.idBookLki=blk.idBookLki  ");
			if (idBookLkiFoto != null)
			{
				query.AppendLine(where + "blf.idBookLkiFoto=@idBookLkiFoto ");
				where = "AND ";
			}
			if (idBookLki != null)
			{
				query.AppendLine(where + "blf.idBookLki=@idBookLki ");
				where = "AND ";
			}


			query.AppendLine("ORDER BY blf.idBookLkiFoto ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			if (idBookLkiFoto!= null) cmd.Parameters.Add(getParameter(cmd, "@idBookLkiFoto", idBookLkiFoto));
			if (idBookLki!= null) cmd.Parameters.Add(getParameter(cmd, "@idBookLki", idBookLki));


			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();

				List<BooklkifotoVO> listBooklkifoto = new List<BooklkifotoVO>();

				while (reader.Read())
				{
					BooklkifotoVO booklkifotoVO = BooklkifotoVO.CreateVO();
					booklkifotoVO.idBookLkiFoto = reader.GetInt32(reader.GetOrdinal("blf_idBookLkiFoto"));
					booklkifotoVO.idBookLki = reader.GetInt32(reader.GetOrdinal("blf_idBookLki"));
					if (!reader.IsDBNull(reader.GetOrdinal("blf_fotoMime")))
						booklkifotoVO.fotoMime = reader.GetString(reader.GetOrdinal("blf_fotoMime"));
					if (!reader.IsDBNull(reader.GetOrdinal("blf_fotoNome")))
						booklkifotoVO.fotoNome = reader.GetString(reader.GetOrdinal("blf_fotoNome"));
					booklkifotoVO.destaque = reader.GetString(reader.GetOrdinal("blf_destaque")).Equals("S");
					booklkifotoVO.cadastro = reader.GetDateTime(reader.GetOrdinal("blf_cadastro"));
					booklkifotoVO.cadastradoPor = reader.GetString(reader.GetOrdinal("blf_cadastradoPor"));
					booklkifotoVO.edicao = reader.GetDateTime(reader.GetOrdinal("blf_edicao"));
					booklkifotoVO.editadoPor = reader.GetString(reader.GetOrdinal("blf_editadoPor"));


					listBooklkifoto.Add(booklkifotoVO);
				}
				reader.Close();
				return listBooklkifoto;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}

		public void setBooklkifoto(BooklkifotoVO booklkifotoVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("UPDATE booklkifoto ");
			query.AppendLine("SET idBookLki=@idBookLki, fotoMime=@fotoMime, fotoNome=@fotoNome, destaque=@destaque,  cadastro=@cadastro, cadastradoPor=@cadastradoPor, edicao=@edicao, editadoPor=@editadoPor ");
			query.AppendLine("WHERE idBookLkiFoto=@idBookLkiFoto ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idBookLkiFoto", booklkifotoVO.idBookLkiFoto));
			cmd.Parameters.Add(getParameter(cmd, "@idBookLki", booklkifotoVO.idBookLki));
			cmd.Parameters.Add(getParameter(cmd, "@fotoMime", booklkifotoVO.fotoMime));
			cmd.Parameters.Add(getParameter(cmd, "@fotoNome", booklkifotoVO.fotoNome));
			cmd.Parameters.Add(getParameter(cmd, "@destaque", (booklkifotoVO.destaque==null?" ":(booklkifotoVO.destaque.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@cadastro", booklkifotoVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@cadastradoPor", booklkifotoVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@edicao", booklkifotoVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@editadoPor", booklkifotoVO.editadoPor));

			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}

		public long newBooklkifoto(BooklkifotoVO booklkifotoVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("INSERT INTO booklkifoto ");
			query.AppendLine("  ( idBookLki, fotoMime, fotoNome, destaque, cadastro, cadastradoPor, edicao, editadoPor ) ");
			query.AppendLine("VALUES ( @idBookLki, @fotoMime, @fotoNome, @destaque, @cadastro, @cadastradoPor, @edicao, @editadoPor ) ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idBookLki", booklkifotoVO.idBookLki));
			cmd.Parameters.Add(getParameter(cmd, "@fotoMime", booklkifotoVO.fotoMime));
			cmd.Parameters.Add(getParameter(cmd, "@fotoNome", booklkifotoVO.fotoNome));
			cmd.Parameters.Add(getParameter(cmd, "@destaque", (booklkifotoVO.destaque==null?" ":(booklkifotoVO.destaque.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@cadastro", booklkifotoVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@cadastradoPor", booklkifotoVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@edicao", booklkifotoVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@editadoPor", booklkifotoVO.editadoPor));


			try
			{
				cmd.ExecuteNonQuery();

				long newID = 0;
				if (tipo.Equals(DBType.MSSql))
				{
					cmd.CommandText = "SELECT IDENT_CURRENT('booklkifoto') AS 'Identity' ";
					DbDataReader reader = cmd.ExecuteReader();
					reader.Read();
					newID = (long)reader.GetDecimal(reader.GetOrdinal("Identity"));
					reader.Close();
				}
				else if (tipo.Equals(DBType.MySql))
				{
					newID = ((MySql.Data.MySqlClient.MySqlCommand)cmd).LastInsertedId;
				}
				else if (tipo.Equals(DBType.ODBC))
				{
					//TODO implementar
				}
				else if (tipo.Equals(DBType.ORACLE))
				{
					//TODO implementar
				}
				return newID;
			}
			catch (Exception ex) { throw ex; }

		}

		public void delBooklkifoto(BooklkifotoVO booklkifotoVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("DELETE FROM booklkifoto ");
			query.AppendLine("WHERE idBookLkiFoto=@idBookLkiFoto ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idBookLkiFoto", booklkifotoVO.idBookLkiFoto));


			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}
		public int getCountBooklkifotoxIdBookLki(int? idBookLki)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idBookLki) as ocorrencias FROM booklkifoto WHERE IdBookLki=@IdBookLki ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdBookLki", idBookLki));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
		public void setBooklkifotoFoto(BooklkifotoVO booklkifotoVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("UPDATE booklkifoto ");
			query.AppendLine("SET foto=@foto, fotoMime=@fotoMime, fotoNome=@fotoNome ");
			query.AppendLine("WHERE idBookLkiFoto=@idBookLkiFoto ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idBookLkiFoto", booklkifotoVO.idBookLkiFoto));

			cmd.Parameters.Add(getParameter(cmd, "@foto", booklkifotoVO.foto));
			cmd.Parameters.Add(getParameter(cmd, "@fotoMime", booklkifotoVO.fotoMime));
			cmd.Parameters.Add(getParameter(cmd, "@fotoNome", booklkifotoVO.fotoNome));

			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}

		public void getBooklkifotoFoto(BooklkifotoVO booklkifotoVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT foto as blf_foto, fotoMime as blf_fotoMime, fotoNome as blf_fotoNome ");
			query.AppendLine("FROM booklkifoto ");
			query.AppendLine("WHERE idBookLkiFoto=@idBookLkiFoto ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idBookLkiFoto", booklkifotoVO.idBookLkiFoto));


			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();

				if (reader.Read())
				{
					if (!reader.IsDBNull(reader.GetOrdinal("blf_foto")))
						booklkifotoVO.foto = getBytesFromBlob(reader, "blf_foto");

					if (!reader.IsDBNull(reader.GetOrdinal("blf_fotoMime")))
						booklkifotoVO.fotoMime = reader.GetString(reader.GetOrdinal("blf_fotoMime"));
					if (!reader.IsDBNull(reader.GetOrdinal("blf_fotoNome")))
						booklkifotoVO.fotoNome = reader.GetString(reader.GetOrdinal("blf_fotoNome"));

				}
				reader.Close();
				return;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}

	}
}
